#-*- coding: utf-8 -*-
from sqlalchemy import MetaData, Column, Table, ForeignKey
from sqlalchemy import Integer, String
from sqlalchemy import select, func, distinct
from sqlalchemy import and_
from sqlalchemy import create_engine
import os
import csv
	
class CLS_Modl_Queries():
	
	''' Model -> Query's '''

	def exe_query_1(self, session, CLS_Table):

		''' Query Principal ->  Cursos tenham as palavras computadores e informatica - retorna uma lista - all() '''

		lista_todos_dados = self.get_all_data(session, CLS_Table)
		lista_query1 = []	# lista que vai conter lista de objectos de cada linha para query1, lista a escrever no csv
		
		for obj_query in lista_todos_dados:
			# listas_anos - lista com os anos e os respectivos numero de alunos (homens e mulheres)
			listas_anos_hm=[('9596' , obj_query.year_9596hm), ('9697' , obj_query.year_9697hm), ('9798' , obj_query.year_9798hm), ('9899' , obj_query.year_9899hm), ('9900' , obj_query.year_9900hm), ('0001' , obj_query.year_0001hm), ('0102' , obj_query.year_0102hm), ('0203' , obj_query.year_0203hm)
				, ('0304' , obj_query.year_0304hm), ('0405' , obj_query.year_0405hm), ('0506' , obj_query.year_0506hm), ('0607' , obj_query.year_0607hm), ('0708' , obj_query.year_0708hm), ('0809' , obj_query.year_0809hm), ('0910' , obj_query.year_0910hm), ('1011' , obj_query.year_1011hm)]
			
			# linha_csv_query1 - lista com o estabelecimento, unidade organica e curso, posteriormente sao adicionados os anos de funcionamento do curso
			linha_csv_query1 = [obj_query.estabelecimento.encode('utf-8'), obj_query.unidade.encode('utf-8'), obj_query.curso.encode('utf-8')]

		
			for lista_ano in listas_anos_hm:		#para cada lista com o ano(lista_ano[0]) e nr de alunos(lista_ano[1]), da lista de anos
				if(lista_ano[1] != '0'):
					linha_csv_query1.append(lista_ano[0])	# append do ano em que funcionou
					#linha_csv_query2.append(lista_ano[1])	# append do nr de alunos
			lista_query1.append(linha_csv_query1)
			#lista_query2.append(linha_csv_query2)
			
		return (lista_query1)
		
	def exe_query_2(self, session, CLS_Table):

		''' Query -> 2ª Query enunciado '''
		
		lista_query2_dados = self.get_estab_uni_curso(session, CLS_Table)
		lista_query2 = []	# lista que vai conter lista de objectos de cada linha para querr2, lista a escrever no csv

		for curso in lista_query2_dados:
			#			lista_query2.append(('9596', curso[1], curso[2].encode('utf-8'), lista_query2_qnt))
			lista_query2_qnt = session.query(func.sum(CLS_Table.year_9596hm)).filter(and_(CLS_Table.curso == curso[2], CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'))).scalar()
			lista_query2.append(('9596', curso[1].encode('utf-8'), curso[2].encode('utf-8'), lista_query2_qnt))
			lista_query2_qnt = session.query(func.sum(CLS_Table.year_9697hm)).filter(and_(CLS_Table.curso == curso[2], CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'))).scalar()
			lista_query2.append(('9697', curso[1].encode('utf-8'),curso[2].encode('utf-8'), lista_query2_qnt))
			lista_query2_qnt = session.query(func.sum(CLS_Table.year_9798hm)).filter(and_(CLS_Table.curso == curso[2], CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'))).scalar()
			lista_query2.append(('9798', curso[1].encode('utf-8'),curso[2].encode('utf-8'), lista_query2_qnt))
			lista_query2_qnt = session.query(func.sum(CLS_Table.year_9899hm)).filter(and_(CLS_Table.curso == curso[2], CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'))).scalar()
			lista_query2.append(('9899', curso[1].encode('utf-8'),curso[2].encode('utf-8'), lista_query2_qnt))
			lista_query2_qnt = session.query(func.sum(CLS_Table.year_9900hm)).filter(and_(CLS_Table.curso == curso[2], CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'))).scalar()
			lista_query2.append(('9900', curso[1].encode('utf-8'),curso[2].encode('utf-8'), lista_query2_qnt))
			lista_query2_qnt = session.query(func.sum(CLS_Table.year_0001hm)).filter(and_(CLS_Table.curso == curso[2], CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'))).scalar()
			lista_query2.append(('0001', curso[1].encode('utf-8'),curso[2].encode('utf-8'), lista_query2_qnt))
			lista_query2_qnt = session.query(func.sum(CLS_Table.year_0102hm)).filter(and_(CLS_Table.curso == curso[2], CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'))).scalar()
			lista_query2.append(('0102', curso[1].encode('utf-8'),curso[2].encode('utf-8'), lista_query2_qnt))
			lista_query2_qnt = session.query(func.sum(CLS_Table.year_0203hm)).filter(and_(CLS_Table.curso == curso[2], CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'))).scalar()
			lista_query2.append(('0203', curso[1].encode('utf-8'),curso[2].encode('utf-8'), lista_query2_qnt))
			lista_query2_qnt = session.query(func.sum(CLS_Table.year_0304hm)).filter(and_(CLS_Table.curso == curso[2], CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'))).scalar()
			lista_query2.append(('0304', curso[1].encode('utf-8'),curso[2].encode('utf-8'), lista_query2_qnt))
			lista_query2_qnt = session.query(func.sum(CLS_Table.year_0405hm)).filter(and_(CLS_Table.curso == curso[2], CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'))).scalar()
			lista_query2.append(('0405', curso[1].encode('utf-8'),curso[2].encode('utf-8'), lista_query2_qnt))
			lista_query2_qnt = session.query(func.sum(CLS_Table.year_0506hm)).filter(and_(CLS_Table.curso == curso[2], CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'))).scalar()
			lista_query2.append(('0506', curso[1].encode('utf-8'),curso[2].encode('utf-8'), lista_query2_qnt))
			lista_query2_qnt = session.query(func.sum(CLS_Table.year_0607hm)).filter(and_(CLS_Table.curso == curso[2], CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'))).scalar()
			lista_query2.append(('0607', curso[1].encode('utf-8'),curso[2].encode('utf-8'), lista_query2_qnt))
			lista_query2_qnt = session.query(func.sum(CLS_Table.year_0708hm)).filter(and_(CLS_Table.curso == curso[2], CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'))).scalar()
			lista_query2.append(('0708', curso[1].encode('utf-8'),curso[2].encode('utf-8'), lista_query2_qnt))
			lista_query2_qnt = session.query(func.sum(CLS_Table.year_0809hm)).filter(and_(CLS_Table.curso == curso[2], CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'))).scalar()
			lista_query2.append(('0809', curso[1].encode('utf-8'),curso[2].encode('utf-8'), lista_query2_qnt))
			lista_query2_qnt = session.query(func.sum(CLS_Table.year_0910hm)).filter(and_(CLS_Table.curso == curso[2], CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'))).scalar()
			lista_query2.append(('0910', curso[1].encode('utf-8'),curso[2].encode('utf-8'), lista_query2_qnt))
			lista_query2_qnt = session.query(func.sum(CLS_Table.year_1011hm)).filter(and_(CLS_Table.curso == curso[2], CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'))).scalar()
			lista_query2.append(('1011', curso[1].encode('utf-8'),curso[2].encode('utf-8'), lista_query2_qnt))
		return lista_query2
		
	def exe_query_3(self, session, CLS_Table):

		''' Query -> 3ª Query enunciado '''

		###### Query 3 - quantidade de cursos por nivel de formacao
		# lista_query3_niveis - lista com os diferentes tipos de niveis de formacao para cursos com computadores e informatica
		lista_query3_niveis = self.get_niveis(session, CLS_Table)	
		lista_query3 = [] # lista que vai conter o ano, nivel de formacao e numero de  cursos por nivel
		
		for nivel in lista_query3_niveis:
			# lista_query3_qnt - lista com a quantidade de cursos que existem por nivel de formacao e por anos
			lista_query3_qnt = session.query(func.count(CLS_Table.curso)).filter(and_(CLS_Table.nivel == nivel[0], CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'), CLS_Table.year_9596hm != '0')).scalar()
			lista_query3.append(('9596', nivel[0].encode('utf-8'), lista_query3_qnt))
			lista_query3_qnt = session.query(func.count(CLS_Table.curso)).filter(and_(CLS_Table.nivel == nivel[0], CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'), CLS_Table.year_9697hm != '0')).scalar()
			lista_query3.append(('9697', nivel[0].encode('utf-8'), lista_query3_qnt))
			lista_query3_qnt = session.query(func.count(CLS_Table.curso)).filter(and_(CLS_Table.nivel == nivel[0], CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'), CLS_Table.year_9798hm != '0')).scalar()
			lista_query3.append(('9798', nivel[0].encode('utf-8'), lista_query3_qnt))
			lista_query3_qnt = session.query(func.count(CLS_Table.curso)).filter(and_(CLS_Table.nivel == nivel[0], CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'), CLS_Table.year_9899hm != '0')).scalar()
			lista_query3.append(('9899', nivel[0].encode('utf-8'), lista_query3_qnt))
			lista_query3_qnt = session.query(func.count(CLS_Table.curso)).filter(and_(CLS_Table.nivel == nivel[0], CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'), CLS_Table.year_9900hm != '0')).scalar()
			lista_query3.append(('9900', nivel[0].encode('utf-8'), lista_query3_qnt))
			lista_query3_qnt = session.query(func.count(CLS_Table.curso)).filter(and_(CLS_Table.nivel == nivel[0], CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'), CLS_Table.year_0001hm != '0')).scalar()
			lista_query3.append(('0001', nivel[0].encode('utf-8'), lista_query3_qnt))
			lista_query3_qnt = session.query(func.count(CLS_Table.curso)).filter(and_(CLS_Table.nivel == nivel[0], CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'), CLS_Table.year_0102hm != '0')).scalar()
			lista_query3.append(('0102', nivel[0].encode('utf-8'), lista_query3_qnt))
			lista_query3_qnt = session.query(func.count(CLS_Table.curso)).filter(and_(CLS_Table.nivel == nivel[0], CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'), CLS_Table.year_0203hm != '0')).scalar()
			lista_query3.append(('0203', nivel[0].encode('utf-8'), lista_query3_qnt))
			lista_query3_qnt = session.query(func.count(CLS_Table.curso)).filter(and_(CLS_Table.nivel == nivel[0], CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'), CLS_Table.year_0304hm != '0')).scalar()
			lista_query3.append(('0304', nivel[0].encode('utf-8'), lista_query3_qnt))
			lista_query3_qnt = session.query(func.count(CLS_Table.curso)).filter(and_(CLS_Table.nivel == nivel[0], CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'), CLS_Table.year_0405hm != '0')).scalar()
			lista_query3.append(('0405', nivel[0].encode('utf-8'), lista_query3_qnt))
			lista_query3_qnt = session.query(func.count(CLS_Table.curso)).filter(and_(CLS_Table.nivel == nivel[0], CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'), CLS_Table.year_0506hm != '0')).scalar()
			lista_query3.append(('0506', nivel[0].encode('utf-8'), lista_query3_qnt))
			lista_query3_qnt = session.query(func.count(CLS_Table.curso)).filter(and_(CLS_Table.nivel == nivel[0], CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'), CLS_Table.year_0607hm != '0')).scalar()
			lista_query3.append(('0607', nivel[0].encode('utf-8'), lista_query3_qnt))
			lista_query3_qnt = session.query(func.count(CLS_Table.curso)).filter(and_(CLS_Table.nivel == nivel[0], CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'), CLS_Table.year_0708hm != '0')).scalar()
			lista_query3.append(('0708', nivel[0].encode('utf-8'), lista_query3_qnt))
			lista_query3_qnt = session.query(func.count(CLS_Table.curso)).filter(and_(CLS_Table.nivel == nivel[0], CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'), CLS_Table.year_0809hm != '0')).scalar()
			lista_query3.append(('0809', nivel[0].encode('utf-8'), lista_query3_qnt))
			lista_query3_qnt = session.query(func.count(CLS_Table.curso)).filter(and_(CLS_Table.nivel == nivel[0], CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'), CLS_Table.year_0910hm != '0')).scalar()
			lista_query3.append(('0910', nivel[0].encode('utf-8'), lista_query3_qnt))
			lista_query3_qnt = session.query(func.count(CLS_Table.curso)).filter(and_(CLS_Table.nivel == nivel[0], CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'), CLS_Table.year_1011hm != '0')).scalar()
			lista_query3.append(('1011', nivel[0].encode('utf-8'), lista_query3_qnt))
		return lista_query3


	def exe_query_4(self, session, CLS_Table):

		''' Query -> 4ª Query enunciado '''

		###### Query 4 - quantidade de alunos por nivel de formacao
		#lista_query4_niveis = [(nivel) for nivel in lista_query3_niveis]	# copia da lista de niveis da query 3
		lista_query4_niveis = self.get_niveis(session, CLS_Table)
		lista_query4 = []	# lista que vai conter o ano o nivel e a quantidade de alunos por anos
		
		for nivel in lista_query4_niveis:
			# lista_query4_qnt - lista com a quantidade de alunos que existem por nivel de formacao e por anos
			lista_query4_qnt = session.query(func.sum(CLS_Table.year_9596hm)).filter(and_(CLS_Table.nivel == nivel[0], CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'))).scalar()
			lista_query4.append(('9596', nivel[0].encode('utf-8'), lista_query4_qnt))
			lista_query4_qnt = session.query(func.sum(CLS_Table.year_9697hm)).filter(and_(CLS_Table.nivel == nivel[0], CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'))).scalar()
			lista_query4.append(('9697', nivel[0].encode('utf-8'), lista_query4_qnt))
			lista_query4_qnt = session.query(func.sum(CLS_Table.year_9798hm)).filter(and_(CLS_Table.nivel == nivel[0], CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'))).scalar()
			lista_query4.append(('9798', nivel[0].encode('utf-8'), lista_query4_qnt))
			lista_query4_qnt = session.query(func.sum(CLS_Table.year_9899hm)).filter(and_(CLS_Table.nivel == nivel[0], CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'))).scalar()
			lista_query4.append(('9899', nivel[0].encode('utf-8'), lista_query4_qnt))
			lista_query4_qnt = session.query(func.sum(CLS_Table.year_9900hm)).filter(and_(CLS_Table.nivel == nivel[0], CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'))).scalar()
			lista_query4.append(('9900', nivel[0].encode('utf-8'), lista_query4_qnt))
			lista_query4_qnt = session.query(func.sum(CLS_Table.year_0001hm)).filter(and_(CLS_Table.nivel == nivel[0], CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'))).scalar()
			lista_query4.append(('0001', nivel[0].encode('utf-8'), lista_query4_qnt))
			lista_query4_qnt = session.query(func.sum(CLS_Table.year_0102hm)).filter(and_(CLS_Table.nivel == nivel[0], CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'))).scalar()
			lista_query4.append(('0102', nivel[0].encode('utf-8'), lista_query4_qnt))
			lista_query4_qnt = session.query(func.sum(CLS_Table.year_0203hm)).filter(and_(CLS_Table.nivel == nivel[0], CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'))).scalar()
			lista_query4.append(('0203', nivel[0].encode('utf-8'), lista_query4_qnt))
			lista_query4_qnt = session.query(func.sum(CLS_Table.year_0304hm)).filter(and_(CLS_Table.nivel == nivel[0], CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'))).scalar()
			lista_query4.append(('0304', nivel[0].encode('utf-8'), lista_query4_qnt))
			lista_query4_qnt = session.query(func.sum(CLS_Table.year_0405hm)).filter(and_(CLS_Table.nivel == nivel[0], CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'))).scalar()
			lista_query4.append(('0405', nivel[0].encode('utf-8'), lista_query4_qnt))
			lista_query4_qnt = session.query(func.sum(CLS_Table.year_0506hm)).filter(and_(CLS_Table.nivel == nivel[0], CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'))).scalar()
			lista_query4.append(('0506', nivel[0].encode('utf-8'), lista_query4_qnt))
			lista_query4_qnt = session.query(func.sum(CLS_Table.year_0607hm)).filter(and_(CLS_Table.nivel == nivel[0], CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'))).scalar()
			lista_query4.append(('0607', nivel[0].encode('utf-8'), lista_query4_qnt))
			lista_query4_qnt = session.query(func.sum(CLS_Table.year_0708hm)).filter(and_(CLS_Table.nivel == nivel[0], CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'))).scalar()
			lista_query4.append(('0708', nivel[0].encode('utf-8'), lista_query4_qnt))
			lista_query4_qnt = session.query(func.sum(CLS_Table.year_0809hm)).filter(and_(CLS_Table.nivel == nivel[0], CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'))).scalar()
			lista_query4.append(('0809', nivel[0].encode('utf-8'), lista_query4_qnt))
			lista_query4_qnt = session.query(func.sum(CLS_Table.year_0910hm)).filter(and_(CLS_Table.nivel == nivel[0], CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'))).scalar()
			lista_query4.append(('0910', nivel[0].encode('utf-8'), lista_query4_qnt))
			lista_query4_qnt = session.query(func.sum(CLS_Table.year_1011hm)).filter(and_(CLS_Table.nivel == nivel[0], CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'))).scalar()
			lista_query4.append(('1011', nivel[0].encode('utf-8'), lista_query4_qnt))
		return lista_query4

	def get_all_data(self, session, CLS_Table):

		''' Model -> Funcao que efectua uma query a bdd em que retorna uma lista com todos os campos da tabela, em que o curso tenha computadores e informatica
		var session - sessao para acedar a bdd
		var CLS_Table - objecto da bdd
		return data - lista com todos os dados da bdd
		'''
		
		data = session.query(CLS_Table).filter(and_(CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'))).all()
		return data
		
	def get_estab_uni_curso(self, session, CLS_Table):
		
		''' Model -> Funcao que efectua a query a base de dados, retornando os estabelcimentos, unidades organicas e cursos.
		var session - sessao para acedar a bdd
		var CLS_Table - objecto da bdd
		'''

		estab_uni_curso = session.query(CLS_Table.estabelecimento, CLS_Table.unidade, CLS_Table.curso).filter(and_(CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'))).all()
		return estab_uni_curso
		
	def get_curso(self, session, CLS_Table, unidade):

		''' Model -> Funcao que efectua uma query a bdd e retorna uma lista com os cursos existentes.
		var session - sessao para acedar a bdd
		var CLS_Table - objecto da bdd
		'''

		curso = session.query(distinct(CLS_Table.curso)).filter(and_(CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'), CLS_Table.unidade == unidade)).all()
		return curso
		
	def get_unidades(self, session, CLS_Table):

		''' Model -> Funcao que efectua uma query a bdd e retorna uma lista com os cursos existentes.
		var session - sessao para acedar a bdd
		var CLS_Table - objecto da bdd
		'''

		unidade = session.query(distinct(CLS_Table.unidade)).filter(and_(CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'))).all()
		return unidade
		
	def get_nivel_data(self, session, CLS_Table, unidade):

		''' Model -> Funcao que efectua uma query a bdd em que retorna uma lista com todos os niveis da tabela, 
		em que o curso tenha computadores e informatica e, a unidade seja igual
		var session - sessao para acedar a bdd
		var CLS_Table - objecto da bdd
		var unidade - unidade escolhida
		return nivel_data - lista com os diferentes niveis
		'''

		nivel_data = session.query(distinct(CLS_Table.nivel)).filter(and_(CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%'), CLS_Table.unidade == unidade))
		return nivel_data

	def get_niveis(self, session, CLS_Table):
		
		''' Model -> Funcao que efectua uma query a bdd em que retorna uma lista com todos os niveis da tabela, 
		em que o curso tenha computadores e informatica
		var session - sessao para acedar a bdd
		var CLS_Table - objecto da bdd
		return nivel_data - lista com os diferentes niveis
		'''

		niveis = session.query(distinct(CLS_Table.nivel)).filter(and_(CLS_Table.curso.like("%Computadores%"), CLS_Table.curso.like(u'%Informática%')))
		return niveis

